<?php
/**
 * 物资名称分类
 */
namespace App\Http\Controllers\Api;
use App\Model\Goods;
use App\Model\Stock;
use App\Model\MaterialList;
use App\Model\Users;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Auth;
use DB;
class MaterialListController extends Controller
{
    /**
     * 物资名称列表数据操作
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function list_data(Request $request)
    {
        $query = $this->pub($request);
        $res=$query->paginate($request->get('limit',15))->toArray();
        $user = Auth::user();
        $role = $user->role;
        $community = $user->community;
        foreach ($res['data'] as $key=>$val){
            $stock_count = Stock::with(['company:id,community'])->select(DB::raw('SUM(`income_warehouse`) as income_warehouse,SUM(`leave_warehouse`) as leave_warehouse,SUM(`loss_warehouse`) as loss_warehouse,SUM(`warehouse`) as warehouse'))->where('material_id',$val['id'])->where('goods_id',$val['goods_id']);
            if(isset($request['checkcategory_id']) && $checkcategory_id = $request['checkcategory_id']){ //所属单位id
                $stock_count = $stock_count->where('checkcategory_id',$checkcategory_id);
            }

            if(Users::iscommunity($role)){
                $stock_count = $stock_count->whereHas('company', function ($q) use ($community) {
                    $q->where('community', $community);
                });
                $stock_count = $stock_count->first();
                if($stock_count){
                    $stock_count = $stock_count->toArray();
                }
                $res['data'][$key]['income_warehouse'] = $stock_count['income_warehouse']??0;
                $res['data'][$key]['leave_warehouse'] = $stock_count['leave_warehouse']??0;
                $res['data'][$key]['loss_warehouse'] = $stock_count['loss_warehouse']??0;
                $res['data'][$key]['warehouse'] = $stock_count['warehouse']??0;
            }else{
                $stock_count = $stock_count->first();
                if($stock_count){
                    $stock_count = $stock_count->toArray();
                }
                $res['data'][$key]['income_warehouse'] = $stock_count['income_warehouse']??0;
                $res['data'][$key]['leave_warehouse'] = $stock_count['leave_warehouse']??0;
                $res['data'][$key]['loss_warehouse'] = $stock_count['loss_warehouse']??0;
                $res['data'][$key]['warehouse'] = $stock_count['warehouse']??0;
            }
			$res['data'][$key]['goods_id_arr'] = $val['goods_id_arr'] ? json_decode($val['goods_id_arr']) : '';

        }


        $res_arr['data']['list'] = $res['data'];
        $res_arr['data']['count'] = $res['total'];
        $res_arr["code"] = 200;
        $res_arr["message"] = 'success';
        return response()->json($res_arr);
    }

    /**
     * 导出物资名称列表
     * @param $request
     * @return array
     */
    public function pub($request){
        $createdat_sort = $request['createdat_sort'];
        $query= MaterialList::with(['goods']);
        /*'stock'=>function($q) use ($createdat_sort){
            $q->select('id','goods_id','material_id','checkcategory_id','created_at');
            if($createdat_sort==1){  //正序
                $q->orderBy('created_at','asc');
            }elseif ($createdat_sort==2){  //倒序
                $q->orderBy('created_at','desc');
            }
        } ->orderby('id','asc')*/
        if(isset($request['checkcategory_id']) && $checkcategory_id = $request['checkcategory_id']){ //所属单位id
            /*$query->whereHas('stock', function ($q) use ($checkcategory_id) {
                $q->where('checkcategory_id', $checkcategory_id);
            });*/
            $stock = Stock::where('checkcategory_id',$checkcategory_id)->get()->toArray();
            $material_ids = array_column($stock,'material_id');
            $query = $query->whereIn('id',$material_ids);
        }
        if(isset($request['goods_id']) && $goods_id = $request['goods_id'] && $request['goods_id']!=='""' ){ //分类id
            $query = $query->where('goods_id',$request['goods_id']);
        }
        if(isset($request['name']) && $name = $request['name']){
            $query = $query->where('name','like','%'.$name.'%');
        }

        if(isset($request['warehouse_sort']) && $warehouse_sort = $request['warehouse_sort']){  //库存数量排序
            if($warehouse_sort==1){  //正序
                $query = $query->orderby('warehouse','asc');
            }elseif ($warehouse_sort==2){  //倒序
                $query = $query->orderby('warehouse','desc');
            }
        }

        if(isset($request['warehouse']) && $warehouse = $request['warehouse']){  //库存数量搜索
            $query = $query->where('warehouse','>=',$warehouse);
        }
        return $query;
    }

    /**
     * 导出物资名称列表
     * @param Request $request
     * @return void
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function exportone(Request $request)
    {
        $query = $this->pub($request);
        $dataExpor= $query->get()->toArray();

        $excel = new \PHPExcel();
        $excel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet = $excel->getActiveSheet();
        $letter = array('A','B','C');
        $title = [
            'A1'=>'序 号',
            'B1'=>'物资名称',
            'C1'=>'分类名称',
        ];
        foreach ($title as $tk=>$tv){
            $objActSheet->setCellValue($tk,$tv);
        }

        foreach ($letter as $l){
            $objActSheet->getStyle($l.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
        }

        /**填充内容*/
        $orirow = 2;
        $i = 1;

        foreach ($dataExpor as &$val) {
            $objActSheet->setCellValue("A".$orirow,$i);
            $objActSheet->setCellValue("B".$orirow,$val['goods']['name']??'');
            $objActSheet->setCellValue("C".$orirow,$val['name']??'');

            $i ++;
            $orirow ++;

        }
        /**填充内容*/
        //统一设置填充内容左右居中、字号、宽度
        foreach ($letter as $l){
            for ($x = 2;$x<=$orirow;$x++){
                /*$objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
                $objActSheet->getStyle($l.'2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中*/
                if($l=='A'){
                    $setHorizontal = \PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
                }else{
                    $setHorizontal = \PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
                }
                $objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal($setHorizontal);
                $objActSheet->getStyle($l.$x)->getFont()->setSize(10);
                $objActSheet->getColumnDimension($l)->setWidth(20);

            }

        }
        /*$objActSheet->getColumnDimension('B')->setWidth(50);
        $objActSheet->getColumnDimension('C')->setWidth(50);
        $objActSheet->getColumnDimension('J')->setWidth(50);*/
        for ($x=1; $x<=($orirow); $x++) {
            $objActSheet->getRowDimension($x)->setRowHeight(30);//统一设置行高度

        }
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                ),
            ),
        );
        $objActSheet->getStyle('A1:C1'.($orirow))->applyFromArray($styleArray);//设置粗边框


        $write = new \PHPExcel_Writer_Excel5($excel);
        ob_end_clean();
        //$date = Carbon::now()->toDateString();//表名前缀日期
        $date=time();
        $filename = '物资名称列表-'.$date;
        $path = '/storage/excel/'.$filename.'.xls';
        $res = $write->save(public_path().$path);
        return response()->json(['code'=>0,'msg'=>'导出成功','url'=>'http://'.$_SERVER['HTTP_HOST'].$path,'file_name'=>$filename]);
    }

    /**
     * 物资分类修改页面
     * @param Request $quest
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\Foundation\Application|\Illuminate\View\View
     */
    public function referweb(Request $request)
    {
        //修改的话该值有值
        $id = isset($request['id']) ? $request['id'] : 0;
        $data = MaterialList::where('id',$id)->first();
        if(empty($data)){
            $res_arr["code"] = 408;
            $res_arr["message"] = '该名称不存在，请联系管理员';
            return response()->json($res_arr);
        }
        $data['goods_id_arr'] = $data['goods_id_arr'] ? json_decode($data['goods_id_arr']) : '';
        $res_arr['data']['data'] = $data;
        $res_arr["code"] = 200;
        $res_arr["message"] = 'success';
        return response()->json($res_arr);
    }

    /**
     * 物资名称数据处理
     * @param Request $quest
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\Foundation\Application|\Illuminate\View\View
     */
    public function refer(Request $request)
    {
        $redata = $request->all();
        $id = isset($redata['id']) ? $redata['id'] : 0;
        $name = isset($redata['name']) ? $redata['name'] : '';
        //$redata['goods_id_arr'] = [1,2];
        if(is_array($redata['goods_id_arr'])){
            $goods_id_arr = count($redata['goods_id_arr']);
            $redata['goods_id'] = isset($redata['goods_id_arr'][$goods_id_arr-1]) ? $redata['goods_id_arr'][$goods_id_arr-1] : 0;
        }
        $redata['goods_id_arr'] = isset($redata['goods_id_arr']) ? json_encode($redata['goods_id_arr']) : '';
        $data = MaterialList::where('id',$id)->first();
        $name_data = MaterialList::where('name',$name)->first();
        if(empty($name)){
            $res_arr["code"] = 408;
            $res_arr["message"] = '名称不能为空，请重新录入';
            return response()->json($res_arr);
        }
        if($data){
            if($name_data && $name_data['id']!=$id){
                $res_arr["code"] = 408;
                $res_arr["message"] = '该名称已存在，请重新录入';
                return response()->json($res_arr);
            }
            if($redata['goods_id']!=$data['goods_id']){  //如果
                $res_stock = Stock::where('goods_id', $data['goods_id'])->where('material_id', $data['id'])->first();
                if($res_stock){
                    $res_arr["code"] = 408;
                    $res_arr["message"] = '该关联关系已被使用，不能更改';
                    return response()->json($res_arr);
                }
            }
            $res = MaterialList::where('id', $id)->update($redata);
        }else{
            if($name_data){
                $res_arr["code"] = 408;
                $res_arr["message"] = '该名称已存在，请重新录入';
                return response()->json($res_arr);
            }
            unset($redata['id']);
            $user = Auth::user();
            $userid = $user->id;
            $redata['user_id'] = $userid;
            $redata['created_at'] = date('Y-m-d H:i:s');
            $res = MaterialList::insertGetId($redata);
        }
        $res_arr["code"] = 200;
        $res_arr["message"] = 'success';
        return response()->json($res_arr);
    }

    /**
     * 物资名称数据删除
     * @param Request $quest
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\Foundation\Application|\Illuminate\View\View
     */
    public function delete(Request $request)
    {
        $redata = $request->all();
        $id = isset($redata['id']) ? $redata['id'] : 0;
        $data = MaterialList::where('id',$id)->first();
        $stock_data = Stock::where('material_id',$id)->first();
        if($stock_data){
            $res_arr["code"] = 408;
            $res_arr["message"] = '该名称已被使用，不能删除';
            return response()->json($res_arr);
        }
        if($data['income_warehouse']!=0 || $data['leave_warehouse']!=0 || $data['loss_warehouse']!=0 || $data['warehouse']!=0){
            $res_arr["code"] = 408;
            $res_arr["message"] = '只有入库、出库、在库、损耗数量均为0时才可删除物资名称';
            return response()->json($res_arr);
        }
        $resu = MaterialList::where('id', $id)->delete();
        $res_arr["code"] = 200;
        $res_arr["message"] = 'success';
        return response()->json($res_arr);
    }

    /**
     * 物资分类列表数据操作
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function material_all(Request $request)
    {
        $goods_id = $request['goods_id']; //所属分类id
        $goods = MaterialList::select('id','name','goods_id');
        if($goods_id){
            $goods = $goods->where('goods_id',$goods_id);
        }
        $goods = $goods->get()->toarray();
        $res_arr['data'] = $goods;
        $res_arr["code"] = 200;
        $res_arr["message"] = 'success';
        return response()->json($res_arr);
    }

    /**
     * 物资库存管理_按物资分类导出
     * @param Request $request
     * @return void
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function exportoneBygid(Request $request)
    {
        $query = $this->pub($request);
        $dataExpor= $query->get()->toArray();

        $excel = new \PHPExcel();
        $excel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet = $excel->getActiveSheet();
        $letter = array('A','B','C','D','E','F','G');
        $title = [
            'A1'=>'序 号',
            'B1'=>'所属分类',
            'C1'=>'物资名称',
            'D1'=>'入库数量',
            'E1'=>'库存数量',
            'F1'=>'出库数量',
            'G1'=>'损耗数量',
        ];
        foreach ($title as $tk=>$tv){
            $objActSheet->setCellValue($tk,$tv);
        }

        foreach ($letter as $l){
            $objActSheet->getStyle($l.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
        }

        /**填充内容*/
        $orirow = 2;
        $i = 1;

        foreach ($dataExpor as &$val) {
            $objActSheet->setCellValue("A".$orirow,$i);
            $objActSheet->setCellValue("B".$orirow,$val['goods']['name']??'');
            $objActSheet->setCellValue("C".$orirow,$val['name']??'');
            $objActSheet->setCellValue("D".$orirow,$val['income_warehouse']??'');
            $objActSheet->setCellValue("E".$orirow,$val['warehouse']??'');
            $objActSheet->setCellValue("F".$orirow,$val['leave_warehouse']??'');
            $objActSheet->setCellValue("G".$orirow,$val['loss_warehouse']??'');

            $i ++;
            $orirow ++;

        }
        /**填充内容*/
        //统一设置填充内容左右居中、字号、宽度
        foreach ($letter as $l){
            for ($x = 2;$x<=$orirow;$x++){
                /*$objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
                $objActSheet->getStyle($l.'2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中*/
                if($l=='A'){
                    $setHorizontal = \PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
                }else{
                    $setHorizontal = \PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
                }
                $objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal($setHorizontal);
                $objActSheet->getStyle($l.$x)->getFont()->setSize(10);
                $objActSheet->getColumnDimension($l)->setWidth(20);

            }

        }
        /*$objActSheet->getColumnDimension('B')->setWidth(50);
        $objActSheet->getColumnDimension('C')->setWidth(50);
        $objActSheet->getColumnDimension('J')->setWidth(50);*/
        for ($x=1; $x<=($orirow); $x++) {
            $objActSheet->getRowDimension($x)->setRowHeight(30);//统一设置行高度

        }
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                ),
            ),
        );
        $objActSheet->getStyle('A1:G1'.($orirow))->applyFromArray($styleArray);//设置粗边框


        $write = new \PHPExcel_Writer_Excel5($excel);
        ob_end_clean();
        //$date = Carbon::now()->toDateString();//表名前缀日期
        $date=time();
        $filename = '物资库存管理_按物资分类导出-'.$date;
        $path = '/storage/excel/'.$filename.'.xls';
        $res = $write->save(public_path().$path);
        return response()->json(['code'=>0,'msg'=>'导出成功','url'=>'http://'.$_SERVER['HTTP_HOST'].$path,'file_name'=>$filename]);
    }

}
